Procedures

Visual Basic modules are made of a declaration section—in which you declare types, constants, and variables used in the module—plus a collection of procedures. These can be of type Sub or Function, depending on whether they return a value to the caller. They can also be Property procedures, but we won't discuss those until Chapter 6. Each procedure has a unique name, a scope, a list of expected arguments, and—if it's a function—a return value.

The scope of a procedure can be Private, Public, or Friend. A Private procedure can be invoked only from within the module in which it's defined. A Public procedure can be invoked from outside the module. If the module is itself Public (a module whose Instancing property isn't 1-Private, contained in a project whose type isn't Standard EXE), the procedure can be called from outside the current program through COM. Since Public is the default scope attribute for procedures, you can always omit it:

' Public function that provides access to a control on a form. Function GetTotal() As Currency GetTotal = CCur(txtTotal.Text) End Function

If the scope isn't Public, you must specify it explicitly:

' All event procedures are Private. Private Sub Form_Load() txtTotal.Text = "" End Sub

The scope of a Friend procedure is halfway between Private and Public: Such a procedure can be called from anywhere in the current project, but not from outside it. This difference becomes important only if you're within a project of a type other than Standard EXE, one that therefore exposes its classes to other applications in the form of COM components. I'll talk about COM components in depth in Chapter 16, but here we need to anticipate some key concepts.

To understand how a Friend procedure can be useful, imagine the following scenario: You have a Public class module that shows a dialog box and asks the user for his or her name and password. It also exposes a GetPassword function so that another module in the project can validate the password and enable or disable specific functions for that particular user. Should you declare this function as Private? No, because it could be invoked from another module. Should the function be Public? No, because that would enable a malicious programmer to query your class module from outside the project and steal users' passwords. (For the sake of simplicity, let's assume that getting a reference to your class isn't a problem.) In this case, the best choice is to make the function a Friend.

If you're within a Standard EXE project, or within a Private class in any type of project, Friend and Public attributes are equivalent because the procedure can't be called from the outside anyway.

Both Sub and Function procedures can accept arguments. Functions also return a value. Setting a reasonable list of expected parameters and a return value is the key to making your procedure more useful. You can pass a procedure any simple data type supported by Visual Basic, including Integer, Boolean, Long, Byte, Single, Double, Currency, Date, String, and Variant. You can also declare your parameter an Object, a Collection, a class defined in your program, or external to it (for example, a Dictionary object). Finally you can pass an array of any of the previously mentioned types. The same is true for the return type too, which can be of any simple type supported by Visual Basic, including arrays—a new Visual Basic 6 feature.

You should be aware that an argument is the value passed to a procedure, whereas a parameter is the value received by it. Both words refer to the same actual value, and the most appropriate term depends on the direction from which you're looking at the value: the caller code sees arguments, and the called procedure sees parameters. In this section, the words argument and parameter are used somewhat interchangeably except where ambiguity would arise.

An argument can be passed by value (using the ByVal keyword) or by reference (using the ByRef keyword or by omitting any qualifier). Arguments passed by reference can be modified by the called procedure, and the modified value can be read back by the caller. Conversely, changes to arguments passed by value are never propagated back to the caller. The rule you should stick to is always pass by reference those arguments that must be modified by the procedure, and pass by value all the others. This approach minimizes the risk of accidentally modifying the value of a variable passed to the method. Let me explain this concept with an example:

' Z is incorrectly declared ByRef. Sub DrawPoint(ByVal X As Long, ByVal Y As Long, Z As Long) ' Keep arguments positive. If X < 0 Then X = 0 If Y < 0 Then Y = 0 If Z < 0 Then Z = 0 ' Probable cause of bugs !!! ' ... End Sub

This procedure modifies its parameters to make them fit their valid range; if a parameter is passed using ByRef, as Z is in the previous example, these changes are propagated to the calling code. This type of bug can be undetected for some time, especially if in most cases you call the procedure using constants or expressions. The fact that your code works in these situations can convince you that the procedure is correct and lull you into a false sense of security:

' This works. (Argument is a constant.) DrawPoint 10, 20, 40 ' This works too. (Argument is an expression.) DrawPoint x * 2, y * 2, z * 2 ' This works but modifies Z if it's negative. DrawPoint x, y, z

Declaring a parameter using ByVal offers another benefit: you can call the procedure passing a variable or an expression of any type and let Visual Basic do the data type conversion for you. Conversely, if a parameter is declared ByRef and you pass a variable, their types must match:

' Assuming that x,y,z aren't Long variables ' (for example, they are either Single or Double) DrawPoint x, y, 100 ' This works, Visual Basic does the conversion. DrawPoint x, y, z ' This doesn't. (ByRef argument type mismatch.)

There is one exception to the above rule, though: If a procedure exposes a ByRef Variant parameter, you can pass really anything to it. You can exploit this feature to write procedures that aren't specific to a particular data type, as you can see in the code below.

' Swap values of any type. Sub Swap(first As Variant, second As Variant) Dim temp As Variant temp = first: first = second: second = temp End Sub

There's another, subtler reason for using the ByVal keyword whenever possible. When a procedure can access the same memory location by means of two or more different names—for instance, a global variable or a module-level variable that's also passed as an argument—that variable is said to be aliased within that procedure. The problem with aliased variables is that they prevent the Visual Basic compiler from generating optimized code that holds variables' values inside CPU registers when it would otherwise be possible to do so. When all variables are passed to procedures and methods by value, it isn't possible for the routine to modify a global value through one of its parameters, and the compiler can produce better code. If you're sure that all the procedures in your program adhere to this restriction, the native compiler can safely optimize your code. To inform Visual Basic that there aren't any aliased variables in your program, open the Project-Properties dialog box, switch to the Compile tab, click on the Advanced Optimizations button, and tick the Assume No Aliasing check box in the dialog box that appears, as you can see in Figure 4-2.

Figure 4-2. The Advanced Optimizations dialog box.

You might have noticed that I didn't mention UDT structures among the data types that can be passed to a procedure or returned from a function. In fact, you can't always pass such structures as an argument to a procedure. Consider these cases:

• If a UDT structure is declared as Private to a module, it can be passed and returned only by Private procedures inside that module.
• If the UDT structure is defined as Public in a standard BAS module, it can be used as an argument for Private and Friend procedures defined in any type of module in the current project. It can also be used as an argument in Public procedures defined in any BAS module of the application, but not in other types of modules (including forms).



• If you want to write a Public procedure that accepts a UDT argument in a module other than a BAS module, you must place a Public Type definition in a Public class module—that is, a module whose Instancing property is different from 1-Private. (You can't therefore place the declaration in form modules, because they are always Private.) Since you must define the UDT structure in a Public class, you can do that only within project types other than Standard EXE projects.

You can't even declare a Public UDT structure in a class module that isn't Public itself. This prevents you from declaring a Public UDT in a Standard EXE project in any modules except standard BAS modules.

There are restrictions when passing Private objects to a procedure, where a private object is defined in your application but not visible outside it. Private objects are those defined by classes whose Instancing property is set to 1-Private, or objects exposed by the Visual Basic library, including forms, controls, and objects such as App, Clipboard, Screen, and Printer. In general, you can neither include such private objects among the arguments of a procedure nor use them as the return value of a function if the procedure can be called from another application through COM. This restriction makes perfect sense. COM arbitrates the exchange of information between the application that provides the object and the programs that use it. COM is able to deal with all the basic data types supported by Visual Basic and with all the Public objects defined by any program in the Windows environment. On the other hand, COM is unable to pass information in a format that is defined within a program, such as a Private class, as you can see in the code snippet below.

' Visual Basic won't compile the following lines ' if this procedure is located in a Public class. Public Sub ClearField(frm As Form) ... End Sub

This restriction isn't enforced if the method is declared as Private or Friend because such a method can't be invoked from another application through COM and can be called only by another module of the current application. In this case, there's no point in limiting the data types that can be passed to the method, and in fact the Visual Basic compiler doesn't complain if a Private data type appears among the arguments or if it is the return value of a method.

' This is compiled without problems, even within a Public class. Friend Sub ClearField(frm As Form) ... End Sub

Visual Basic 4 introduced the ability to include optional parameters in the parameter list of procedures and methods. Optional parameters must always come after regular (required) parameters. Visual Basic 4 supports only optional parameters of Variant type and permits testing for whether a parameter is actually passed by means of the IsMissing function:

' A public method of a form. Sub PrintData1(text As String, Optional color As Variant) If IsMissing(color) Then color = vbWhite ForeColor = color Print text End Sub

Be very careful when you use the IsMissing function because if you assign a value to a missing parameter, this function returns False from that point on. Study this code excerpt, and see why it doesn't work as expected:

Sub PrintData2(text As String, Optional color As Variant) Dim saveColor As Long If IsMissing(color) Then Form1.FontTransparent = False color = vbWhite End If Form1.ForeColor = color Form1.Print text If IsMissing(color) Then ' Next statement will be never executed! Form1.FontTransparent = False End If End Sub

Visual Basic 5 has added the ability to use optional arguments of any type—not just Variant—and to set their default values right in the parameter list. The PrintData1 routine can be rewritten more concisely under Visual Basic 5 and 6 as follows:

Sub PrintData3(text As String, Optional color As Long = vbWhite) Form1.ForeColor = color Form1.Print text End Sub

When a non-Variant optional parameter isn't initialized to a specific default value in the parameter list, the procedure receives a zero value, an empty string, or Nothing, depending on the type of the parameter. The only data types that can't be used with the Optional keyword are UDT structures.

Optional arguments are very handy for writing flexible procedures, but contrary to what some programmers believe, they don't produce more efficient code. The (wrong) assumption is: Since the calling code doesn't have to push the missing values on the stack, fewer CPU statements are executed and the program runs faster. Unfortunately, this isn't true. When an optional argument is omitted, Visual Basic actually pushes a special "missing" value on the stack. So there's no real speed advantage in omitting an optional argument.

The "missing" magic value used by Visual Basic compiler is Error value &H80020004. The IsMissing function does nothing but test the Variant and return True if it contains this value. Incidentally, this explains why the IsMissing function always returns False with any data type different from Variant: Only a Variant variable can hold an Error value. You can't directly create this special value because the CVErr function accepts only values in the range 0 through 65,535. But you can use the following trick:

' Always call this function without any argument. Function MissingValue(Optional DontPassThis As Variant) As Variant MissingValue = DontPassThis End Function

While Optional arguments are a great addition to the VBA language, they surely tend to reduce the readability of your code. Take this statement as an example:

Err.Raise 999, , , "Value out range"

It appears as if the programmer is raising a custom error; unfortunately, there are too many commas, and the Value out of range string falls in the HelpFile field. How many developers can spot this kind of error just by browsing their source code? Fortunately, you can reduce this adverse effect of optional parameters by using named arguments when calling the procedure. Here's how you can correctly rewrite the previous statement:

Err.Raise Number:=999, Description:="Value out of range"

Named arguments let you alter the order in which arguments appear in the line that invokes the procedure, but they don't allow you to omit an argument that isn't optional. All the procedures that you create in Visual Basic automatically support named arguments. For instance, if you have the following routine:

Sub Init(Optional Name As String, Optional DeptID As Integer, _ Optional Salary As Currency) ' ... End Sub

you can call it as follows:

Init Name:="Roscoe Powell", Salary:=80000

You can implement a routine that accepts any number of arguments using the ParamArray keyword:

Function Sum(ParamArray args() As Variant) As Double Dim i As Integer ' All ParamArrays are zero-based. For i = 0 To UBound(args) Sum = Sum + args(i) Next End Function

You call the Sum function as follows:

Print Sum(10, 30, 20) ' Displays "60"

A few simple rules dictate how the ParamArray keyword should be used:

• There can be only one ParamArray keyword, and it must be at the end of the parameter list.
• The array declared by the ParamArray keyword can only be of the Variant type.
• No Optional parameter can precede the ParamArray keyword.

The ParamArray keyword can be an invaluable aid in creating truly generic functions. For instance, you can build a function that returns the maximum of any number of values:

Function Max(first As Variant, ParamArray args() As Variant) As Variant Dim i As Integer Max = first For i = 0 To UBound(args) If args(i) > Max Then Max = args(i) Next End Function

Note that there's one required argument in the previous procedure because it doesn't make sense to evaluate the maximum of 0 values. Even though it isn't documented, you can use the IsMissing function on the args() parameter. Thus, you have two ways to exit the function if no optional values were passed to the routine:

' The documented way If LBound(args) > UBound(args) Then Exit Function ' The undocumented way is more concise and readable If IsMissing(args) Then Exit Function

The ParamArray keyword can be coupled with the ability to return arrays. For example, while the Array function lets you build Variant arrays on the fly, VBA doesn't offer a similar function for building other types of arrays. Here's how you can remedy this problem:

Function ArrayLong(ParamArray args() As Variant) As Long() Dim numEls As Long, i As Long numEls = UBound(args) _ LBound(args) + 1 If numEls <= 0 Then Err.Raise 5 ' Invalid procedure call ReDim result(0 To numEls - 1) As Long For i = 0 To numEls _ 1 result(i) = args(i) Next ArrayLong = result End Function

One last note about the ParamArray keyword: If you want to get the best performances, stay clear of it. It forces you to use Variant parameters, which are the slowest data type supported by Visual Basic. If you need to use optional arguments, use non-Variant Optional parameters, which are much faster.

Error handling is an important feature of the Visual Basic language and is closely related to the structure of your procedures. Visual Basic offers three statements that give you control over what happens when an error occurs during the execution of your code:

• The On Error Resume Next statement tells Visual Basic to ignore any error. When an error actually occurs, Visual Basic proceeds with executing the next statement. You can test the error code using the Err function, or you can ignore it completely.
• The On Error Goto <label> statement tells Visual Basic that any error will cause a jump to the named label, which must be located in the same procedure in which this statement appears. You can use the same label name in different procedures because a label's scope is the procedure, not the module.
• The On Error Goto 0 statement tells Visual Basic to cancel the effect of any active On Error Resume Next or On Error Goto <label> statement. When an error occurs, Visual Basic behaves as if error trapping is disabled.

Selecting one form of error trapping or another depends on your programming style and the requirements of the specific routine, so no rule can be provided that's valid in every case. All the On Error statements clear the current error code.

When you're dealing with files, the On Error Goto <label> statement is often a better choice because in this case there are so many things that can go wrong, and you don't want to test the Err code after every statement. The same concept applies to intensive math routines that are subject to multiple errors, such as division by 0, overflow, and illegal arguments in function calls. In most cases, when an error occurs in these routines, the best you can do is exit right away and report the error to the calling code.

On the other hand, there are many cases when the "error" isn't a fatal error. Suppose that you want your user to insert a given disk in drive A, but you want to give him or her another chance if the disk isn't the one you were expecting instead of aborting the whole procedure when the user inserts a wrong disk. Here's a reusable procedure that lets you check whether a drive contains a disk with a given label and prompts the user to insert a disk if the drive is empty:

Function CheckDisk(ByVal Drive As String, VolumeLabel As String)_ As Boolean Dim saveDir As String, answer As Integer On Error GoTo Error_Handler Drive = Left$(Drive, 1) ' Save the current drive for restoring later. saveDir = CurDir$ ' Next statement is likely to fire an error. ' Check the drive specified in the parameter. ChDrive Drive ' Return True if the label matches, False otherwise. CheckDisk = (StrComp(Dir$(Drive & ":\*.*", vbVolume), _ VolumeLabel, vbTextCompare) = 0) ' Restore the original current drive. ChDrive saveDir Exit Function Error_Handler: ' If error is Device Unavailable or Disk Not Ready, and it's a disk, ' give the user the chance to insert the diskette in the drive. If (Err = 68 Or Err = 71) And InStr(1, "AB", Drive, _ vbTextCompare) Then answer = MsgBox("Please enter a diskette in drive " & Drive, _ vbExclamation + vbRetryCancel) ' Retry the ChDir statement, or exit returning False. If answer = vbRetry Then Resume Else ' In all other cases, return the error to the calling program. Err.Raise Err.Number, Err.Source, Err.Description End If End Function

You can exit from an error routine in at least five ways:

• You can execute a Resume statement to retry the line of code that caused the error.
• You can execute a Resume Next statement to resume execution in the procedure body at the line immediately after the one that caused the error.
• You can execute a Resume <line> statement to resume execution at a given line in the procedure body; <line> can be a line number or label name.
• You can report the error to the calling routine by executing an Err.Raise method.
• You can exit the procedure by executing an Exit Sub or Exit Function statement or by letting the execution flow into the End Sub or End Function directive. In both cases, the calling procedure receives a zero error code.

The On Error Resume Next statement is most useful when you don't expect many errors or when you don't need to trap all of them. In some cases, you can use this approach when the exception can be safely ignored, as in the following example:

' Hide all controls in Form1. Dim ctrl As Control ' Not all controls support the Visible property (Timers don't). On Error Resume Next For Each ctrl In Form1.Controls Ctrl.Visible = False Next

If you want to test an error condition, you must do it immediately after each statement that could cause an error. Or you can test the Err function at the end of a group of statements. In fact, if any statement raises an error, Visual Basic doesn't reset the Err value until the programmer does it explicitly with an Err.Clear method.

If an error occurs while there's an active On Error Resume Next statement, the execution continues with the next statement in the procedure, whichever the next statement is. This feature permits you to test attributes of controls and objects in ways that would be impossible otherwise:

' Hide all visible controls on Form1, and then restore ' their visibility. Dim ctrl As Control, visibleControls As New Collection On Error Resume Next For Each ctrl In Form1.Controls If ctrl.Visible = False Then ' This control doesn't support the Visible property ' or it is already hidden: in either case, don't do anything. Else ' Remember that this is a visible control, and then hide it. visibleControls.Add ctrl ctrl.Visible = False End If Next ' Do whatever you need to do (omitted), and ' then correctly restore the original controls' Visible property. For Each ctrl In visibleControls ctrl.Visible = True Next

This unorthodox way to use On Error Resume Next is a powerful weapon in the hands of expert Visual Basic programmers, but it tends to obscure the logic behind your code. My suggestion is to resort to this technique only if it's impossible or impractical to follow other approaches, and—above all—to add exhaustive comments to your code so that it's clear exactly what you're doing and why.

When a procedure that contains an On Error Resume Next statement exits, the calling code sees the code of the last error that occurred inside the procedure. Compare this behavior with procedures containing an On Error Goto <label> statement, which always clears the error code when the control returns to the calling code.

So far, we've seen what happens when an error fires in a procedure that is protected with an On Error Resume Next or On Error Goto <line> statement. When either one of these statements is currently active (when it hasn't been cancelled by a subsequent On Error Goto 0 statement), the procedure is said to have an active error handler. However, not all procedures are so well written, and in many cases you must consider exactly what happens when Visual Basic fires an error that you aren't prepared to deal with. (These are also known as unanticipated errors.)

• If the procedure has been called by another procedure, Visual Basic immediately terminates the current procedure and reports the error to the calling procedure, at the line that called the now-terminated procedure. If the calling procedure has an active error handler, it deals with the error locally (as if the error actually occurred there); otherwise, it exits immediately and reports the error to the procedure that called it, and so on until Visual Basic finds a pending procedure on the procedure stack that has an active error handler.
• If no procedure on the procedure stack has an active error handler, Visual Basic has no code to notify of the error, so it immediately stops the program with an error. If you are within the IDE, you can now spot the original statement that produced the error so that you can modify the code right away and restart the program. If the error occurred in a compiled EXE program, the application terminates with a fatal error.
• It's important to remember that all event procedures—such as Form_Load or Command1_Click—aren't generally called by code in your application; instead, they're called by the Visual Basic runtime file. So if an error occurs in those event procedures, there's no code to which to delegate the error and the application always terminates with a fatal error. Keep this in mind when distributing your On Error statements, and never omit them in event procedures unless you're 100 percent sure that they can never raise an error.

Here's an example that summarizes what I have said so far. Just add a Command1 button to a form, and then enter the following code:

Private Sub Command1_Click() ' Comment next line to see what happens when an event ' procedure isn't protected against unanticipated errors. On Error GoTo Error_Handler Print EvalExpression(1) Print EvalExpression(0) Print EvalExpression(-1) Exit Sub Error_Handler: Print "Result unavailable" Resume Next End Sub Function EvalExpression(n As Double) As Double On Error GoTo Error_Handler EvalExpression = 1 + SquareRootReciprocal(n) Exit Function Error_Handler: If Err = 11 Then ' If Division by zero, return -1 (no need to Resume). EvalExpression = -1 Else ' Notify the caller that an error occurred. Err.Raise Err.Number, Err.Source, Err.Description End If End Function Function SquareRootReciprocal(n As Double) As Double ' This might cause a Division By Zero error (Err = 11) or ' an Invalid Procedure Call or Argument (Err = 5). SquareRootReciprocal = 1 / Sqr(n) End Function

Run the program, and click on the button. You should see this output:

2 -1 Result Unavailable

Then comment out the On Error statement in the Command1_Click procedure to watch what happens when an event procedure isn't completely protected by an error handler.

Visual Basic automatically associates several useful pieces of information with any error that it fires, and it gives you the ability to do the same when you raise a custom error. This capability is provided through the Err object, which exposes six properties and two methods. The most important property is Number, the numeric error code. This is the default property for this object, so you can use either Err or Err.Number in your code, which permits backward compatibility to be maintained with older versions of Visual Basic, and even QuickBasic.

The Source property is automatically filled with a string that states where the error occurred. If an error occurs in a standard or form module, Visual Basic sets this property to the name of the project (for example, Project1); if an error occurs in a class module, Visual Basic sets this property to the complete name of the class (for example, Project1.Class1). You can test this property to understand whether the error is internal or external to your application, and you can modify it before you notify the calling code of the error.

The Description property is also automatically filled with a string that describes the error that just occurred (for example, "Division by Zero"). In most cases, this string is more descriptive than the mere error code number. You can modify it in code before notifying the caller about the error. The HelpFile and HelpContext properties are filled by Visual Basic with information about which page in a help file contains an additional description of the error, how to handle it, and so on. Each native Visual Basic error corresponds to a page in Visual Basic's own help file. If you write libraries for other developers, you should devise a custom error numbering scheme and associate each custom error code with a page in a help file that you provide to your customers. This is rarely needed with business applications. Finally, the LastDllError is a read-only property that is set by Visual Basic when an error occurs during the processing of an API routine and isn't useful in any other case.

The Raise method raises an error and optionally assigns a value to all the properties seen above. Its syntax is the following:

Err.Raise Number, [Source], [Description], [HelpFile], [HelpContext])

All arguments are optional except the first one. For more readable code, use named arguments, as in this line:

Err.Raise Number:=1001, Description:="Customer Not Found"

The Clear method resets all the properties in one operation.

Visual Basic's Err object is compatible with the COM mechanism of notifying error codes and information among different processes. The importance of this feature will become apparent in Chapter 16.

So far, I've described what happens when an error occurs in a compiled application. When the code is executed inside the IDE, however, Visual Basic behaves in a slightly different way in an attempt to simplify your debugging chores. More precisely, the IDE can behave differently according to the settings found in the General tab of the Options dialog box from the Tools menu, which you can see in Figure 4-3. Here are the possibilities:

• Break On All Errors: All errors stop execution as soon as they occur; this setting enables the programmer to see exactly which errors are raised before they're reported to the calling code.
• Break In Class Module: All errors in class modules stop execution as soon as they occur and before they're returned to the calling code. Class modules might need this special treatment because the calling code could be located in another process if the class is Public. This is the default setting for handling errors in the IDE.
• Break On Unhandled Errors: Errors stop execution only if they aren't handled anywhere in the program. This setting exactly mimics what happens in a compiled application, but during the test phase it might hide what really goes on inside your code. For this reason, you should choose this mode only if you know for sure that all errors are processed correctly. If you set this mode in an application that works like a COM component and provides classes to the outside, no error will ever be trapped in the application's code because such applications always have a caller to transmit the error to.

The settings in the Options dialog box shown in Figure 4-3 are the default settings for the Visual Basic environment and are persistent throughout your sessions. If you want to change the error-handling mode of the current environment without affecting the general setting, right-click inside the code window and select one of the commands in the Toggle submenu, shown in Figure 4-4. This approach is usually faster and lets you work with multiple instances of the IDE, each one with a different error-handling mode.

Figure 4-3. The General tab in the Options dialog box.

Figure 4-4. The Toggle pop-up menu in the Code Editor.

You've finally reached the end of this chapter devoted to Visual Basic data types, and you're now aware of a few subtleties that aren't clearly documented in the language manuals. Now you're ready to inspect the many functions that VBA gives you to process these data types.